﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;
using System.Configuration;
[Serializable()]
public class DataStore
{
    private string ConnectionString;

    private int myTimeOut = (30 * 60);
    public DataStore(SessionKey SessionKey) : this()
    {
    }

    public DataStore()
    {
        string ConnectionStringName = "ConnectionString";
        ConnectionStringSettings ConnectionStringSetting = ConfigurationManager.ConnectionStrings[ConnectionStringName];

        if ((ConnectionStringSetting != null))
        {
            ConnectionString = ConnectionStringSetting.ConnectionString;
        }
        else
        {
            throw new Exception(string.Format("Invalid connection string name - {0}", ConnectionStringName));
        }
    }

    public DataTable GetDataTable(string SQL, SqlParameter[] SQLParams)
    {
        return DoGetData(SQL, SQLParams);
    }

    private SqlCommand GetCommand(string SQL, SqlParameter[] SQLParams)
    {
        SqlCommand mySQLCommand = new SqlCommand();
        mySQLCommand.CommandTimeout = myTimeOut;
        mySQLCommand.CommandText = SQL;

        if ((SQLParams != null))
        {
            for (int i = 0; i < SQLParams.Length ; i++)
            {
                if (SQLParams[i].Value == null)
                {
                    SQLParams[i].Value = DBNull.Value;
                }
                mySQLCommand.Parameters.Add(SQLParams[i]);
            }
        }

        return mySQLCommand;
    }

    private SqlConnection GetConnection()
    {
        SqlConnection myConnection = null;

        if (myConnection == null)
        {
            myConnection = new SqlConnection(ConnectionString);
        }
        return myConnection;
    }

    public DataTable DoGetData(string SQL, SqlParameter[] SQLParams)
    {
        if (SQL.Trim().Length == 0)
        {
            throw new Exception("Cannot execute a blank SQL statement.");
        }
        SqlConnection Connection = GetConnection();
        SqlCommand Command = GetCommand(SQL, SQLParams);
        SqlDataAdapter DataAdaptor = GetDataAdaptor();
        DataSet DataSet = new DataSet();

        try
        {
            Command.Connection = Connection;
            DataAdaptor.SelectCommand = Command;
            DataAdaptor.Fill(DataSet);

        }
        catch (Exception ex)
        {
            ThrowSQLException(ex, SQL);
        }
        finally
        {
            CloseConnection(Connection);
        }

        DataTable dt = DataSet.Tables[0];

        return dt;
    }

    private SqlDataAdapter GetDataAdaptor()
    {
        SqlDataAdapter mySQLDataAdaptor = default(SqlDataAdapter);

        if (mySQLDataAdaptor == null)
        {
            mySQLDataAdaptor = new SqlDataAdapter();
        }
        return mySQLDataAdaptor;
    }

    private void CloseConnection(SqlConnection Connection)
    {
        Connection.Close();
    }

    private void ThrowSQLException(Exception ex, string SQL)
    {
        throw new ApplicationException(string.Format("SQL Exception : {0}", SQL), ex);
    }

    public Int64 ExecuteNonQuery(string SQL, SqlParameter[] SQLParams)
    {
        Int64 Rows = default(Int64);
        SqlConnection Connection = GetConnection();
        SqlCommand Command = GetCommand(SQL, SQLParams);
        Command.Connection = Connection;

        try
        {
            Rows = Command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            ThrowSQLException(ex, SQL);
        }
        finally
        {
            CloseConnection(Connection);
        }
        return Rows;

    }
}
